package com.sduept.nwld.dataserver.manager.cim;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sduept.bigdata.fault.utils.ExportExcelStyleDefine;
import com.sduept.bigdata.document.core.CommonDocumentProperties;
import com.sduept.cim.model.extend.LineSegment;
import com.sduept.cim.model.extend.LineTower;
import com.sduept.core.dao.SuperDAO;

@Service
public class LineTowerManager {
	
	@Autowired
	private SuperDAO dao;
	@Autowired
	private ExportExcelStyleDefine excelStyleDefine;
	@Autowired
	private CommonDocumentProperties pros;
	public LineTower createOrUpdate(LineTower t){
		return dao.createOrUpdate(t, LineTower.class);
	}
	
	public void delete(String id){
		dao.delete(id, LineTower.class);
	}
	
	public void deleteTowersByLineId(String lineId){
		dao.delete(LineTower.class, "lineId=?", new Object[]{lineId});
	}
	
	/**
	 * 根据直流/交流线路id，获得所有杆塔信息(包括正在服役的和退役的)
	 * @param lineId
	 * @return
	 */
	public List<LineTower> getAllLineTowerByLineId(String lineId) {
		return dao.findByCondition(LineTower.class, "lineId=? order by sel", lineId);
	}
	/**
	 * 根据直流/交流线路id，获得杆塔信息（正在服役的）
	 * @param lineId
	 * @return
	 */
	public List<LineTower> getLineTowerInServiceByLineId(String lineId){
		return dao.findByCondition(LineTower.class, "lineId=? and isshow = 1 order by sel", lineId);
	}
	/**
	 * 通过ACLineSegement id,获取距离其实变电站距离不为空的所有杆塔信息
	 * 
	 * @param line_cim_id
	 * @return
	 */
	public List<LineTower> getNotNullLineTower(String lineId) {
		return dao.findByCondition(LineTower.class, "lineId =? and isshow = 1 and distanceFromStartStation is not null ", lineId);
	}

	public Map<String,List<Object>> queryLine() throws Exception {
		Map<String,List<Object>> lts_result=new HashMap<String,List<Object>>();
		//线路分组集合
		String hsql="1=1 group by linename";		
		List<LineTower> lts=dao.findByCondition(LineTower.class, hsql);
		if(lts!=null && !lts.isEmpty()){
			List<Object> lts_single=new ArrayList<Object>();
			List<Object> lts_loop=new ArrayList<Object>();
			List<Object> lts_dc=new ArrayList<Object>();
			List<Object> lts_notower=new ArrayList<Object>();
			for(LineTower lt:lts){
				if(lt.getIsLoop() == 0 && lt.getIsShowInMap()==1){
					lts_single.add(lt);
				}
				else if(lt.getIsLoop() == 1 && lt.getIsShowInMap()==1){
					lts_loop.add(lt);
				}
				else if(lt.getIsLoop() == 0 && lt.getIsShowInMap()==1 && lt.getLineName().contains("直流") ){
					lts_dc.add(lt);
				}
				else if(lt.getIsLoop() == 0 && lt.getIsShowInMap()==3){
					lts_notower.add(lt);
				}
			}
			if(lts_single!=null){
				lts_result.put("lts_single",lts_single);
			}
			if(lts_loop!=null){
				lts_result.put("lts_loop",lts_loop);
			}
			if(lts_dc!=null){
				lts_result.put("lts_dc",lts_dc);
			}
			if(lts_notower!=null){
				lts_result.put("lts_notower",lts_notower);
			}
		}
		return lts_result;
	}
	
	public List<LineTower> queryTower(String linename)throws Exception{
		return dao.findByCondition("voltage='500kV' and linename=？", new Object[]{linename},LineTower.class);
	}
	
	/**
	 * 根据线路ID 距离获取最近的杆塔
	 * 
	 * @param lineId
	 * @param distinct
	 * @return
	 */
	public LineTower getShortestLinetower(String lineId, Double distinct) {
		List<LineTower> list = getNotNullLineTower(lineId);
		LineTower shortestTower = null;
		double diffMin = 0;
		for (int i = 0; i < list.size(); i++) {
			double diff = Math.abs(distinct - list.get(i).getDistanceFromStartStation());
			if (i == 0) {
				diffMin = diff;
				shortestTower = list.get(0);
			} else {
				if (diff < diffMin) {
					diffMin = diff;
					shortestTower = list.get(i);
				}
			}
		}
		return shortestTower;
	}
	
	public LineTower getLineTowerById(String id) {
		return dao.findById(id, LineTower.class);
	}
	
	public List<LineTower> queryLoopLineTowers(String lineId, Double longitude, Double latitude, String towerno) {
		return findLineTowerByConditions(null,lineId,longitude,latitude,towerno);
	}
	
	/**
	 * 按条件查询
	 * @param towerId
	 * @param lineId
	 * @param longitude
	 * @param latitude
	 * @param towerno
	 * @return
	 */
	public List<LineTower> findLineTowerByConditions(String towerId, String lineId, Double longitude, Double latitude, String towerno) {
		String sql = "1=1";
		List<Object> values = new ArrayList<>();
		if(towerId != null) {
			sql += " and id = ?";
			values.add(towerId);
		}
		if(lineId != null) {
			sql += " and lineId = ?";
			values.add(lineId);
		}
		if(longitude != null) {
			sql += " and longitude = ?";
			values.add(longitude);
		}
		if(latitude != null) {
			sql += " and latitude = ?";
			values.add(latitude);
		}
		if(towerno != null) {
			sql += " and towerno = ?";
			values.add(towerno);
		}
		List<LineTower> lineTowers = dao.findByCondition(LineTower.class, sql, values.toArray());
		return lineTowers;
	}
	
	// 导出Excel
	public void exportExcel(List<LineTower> lineTowers,LineSegment line) {
		String exportDir = pros.getExportTempPath();
		String sheetName = "杆塔信息";
		String fileName = "LineTower.xlsx";
		String titleName = "杆塔信息详情";
		String[] columnName = {"所属线路","杆塔号","线段编号","经度","纬度","线路电压","距离起始变电站(米)","距离上一杆塔(米)"};
		// 创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		// 设置列宽
		int m = 256 * 15;
		int[] widthes = new int[] {m, m, m, m, m, m, m, m};
		int index = 0;
		for (int w : widthes) {
			sheet.setColumnWidth(index++, w);
		}
		// 标题样式
		HSSFCellStyle titlestyle = excelStyleDefine.setTitleStyle(wb);
		// 表头样式
		HSSFCellStyle columnstyle = excelStyleDefine.setColumnStyle(wb);
		// 内容样式
		HSSFCellStyle contentstyle = excelStyleDefine.setContentStyle(wb);
		// 创建第0行 标题行
		HSSFRow row0 = sheet.createRow((int) 0);
		row0.setHeightInPoints(50);// 标题的高度
		
		HSSFCell cell00 = row0.createCell(0);// 创建标题第一列
		sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, columnName.length-1)); // 合并第0到第23列
		cell00.setCellValue(titleName);
		cell00.setCellStyle(titlestyle);
		
		// 创建第1行 表头行
		HSSFRow row1 = sheet.createRow((int) 1);
		row1.setHeightInPoints(37);// 设置表头高度
		// 创建列
		for (int i = 0; i < columnName.length; i++) {
			HSSFCell cell = row1.createCell(i);
			cell.setCellValue(columnName[i]);
			cell.setCellStyle(columnstyle);
		}
		// 创建单元格，并设置值
		creatCell(lineTowers, line,contentstyle, sheet);
		// 4.将excel导出到文件中
		FileOutputStream out = null;
		File files = new File(exportDir+fileName);
		try {
			files.createNewFile();
			out = new FileOutputStream(files);
			wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
				wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	private String showTowerNo(LineTower lineTower,LineSegment line) {
		if(lineTower.getSerialno() == 0 && "-1".equals(lineTower.getTowerno())) {
			return line.getStartStName();
		} else if (lineTower.getSerialno() > 0 && "-1".equals(lineTower.getTowerno())) {
			return line.getEndStName();
		} else {
			return lineTower.getTowerno();
		}
	}
	
	private void creatCell(List<LineTower> lineTowers, LineSegment line,HSSFCellStyle contentstyle, HSSFSheet sheet) {
		int i = 0;
		for (LineTower tower : lineTowers) {
			HSSFRow row = sheet.createRow((int) i + 2);
			// 所属线路
			HSSFCell cell0 = row.createCell(0);
			cell0.setCellValue(tower.getLineName());
			cell0.setCellStyle(contentstyle);
			// 杆塔号
			HSSFCell cell1 = row.createCell(1);
			String towerno = showTowerNo(tower,line);
			cell1.setCellValue(towerno);
			cell1.setCellStyle(contentstyle);
			// 线段编号
			HSSFCell cell2 = row.createCell(2);
			cell2.setCellValue(tower.getLineno());
			cell2.setCellStyle(contentstyle);
			// 经度
			HSSFCell cell3 = row.createCell(3);
			cell3.setCellValue(tower.getLongitude());
			cell3.setCellStyle(contentstyle);
			// 纬度
			HSSFCell cell4 = row.createCell(4);
			cell4.setCellValue(tower.getLatitude());
			cell4.setCellStyle(contentstyle);
			// 线路电压
			HSSFCell cell5 = row.createCell(5);
			cell5.setCellValue(tower.getLineVoltage());
			cell5.setCellStyle(contentstyle);
			// 距离起始变电站(米)
			HSSFCell cell6 = row.createCell(6);
			if(null != tower.getDistanceFromStartStation()) {
				cell6.setCellValue(tower.getDistanceFromStartStation());
			} else {
				cell6.setCellValue("");
			}
			cell6.setCellStyle(contentstyle);
			// 距离上一杆塔(米)
			HSSFCell cell7 = row.createCell(7);
			if(null != tower.getDistanceFromLastTower()) {
				cell7.setCellValue(tower.getDistanceFromLastTower());
			} else {
				cell7.setCellValue("");
			}
			cell7.setCellStyle(contentstyle);
			i++;
		}
	} 

}
